package com.lsc.record.core.service.impl;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson2.JSON;
import com.lsc.record.core.entity.DmTypeAttr;
import com.lsc.record.core.entity.TableEntity;
import com.lsc.record.core.entity.TableField;
import com.lsc.record.core.service.TableQueryServcie;
import com.lsc.record.core.util.RowConvertUtil;
import com.mybatisflex.core.row.Db;
import com.mybatisflex.core.row.Row;

@Service
public class TableQueryServiceImpl implements TableQueryServcie {
	
	@Override
	public List<TableEntity> list() {
		List<TableEntity> result = new ArrayList<>();
		String sql = "SELECT TABLE_NAME,TABLE_TYPE,COMMENTS FROM USER_TAB_COMMENTS ORDER BY TABLE_TYPE";
		List<Row> list = Db.selectListBySql(sql);
		for (Row row : list) {
			Map<String,Object> tableData = row.toCamelKeysMap();
			String jsonStr = JSON.toJSONString(tableData);
			TableEntity te = JSON.parseObject(jsonStr, TableEntity.class);
			System.out.println(te.getTableName());
			result.add(te);
		}
		
		return result;
	}
	
	@Override
	public List<TableEntity> list(boolean hasRelation) {
		List<TableEntity> result = new ArrayList<>();
		String sql = "SELECT TABLE_NAME,TABLE_TYPE,COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE 'DM_%' ORDER BY TABLE_TYPE";
		List<Row> list = Db.selectListBySql(sql);
		for (Row row : list) {
			Map<String,Object> tableData = row.toCamelKeysMap();
			String jsonStr = JSON.toJSONString(tableData);
			TableEntity te = JSON.parseObject(jsonStr, TableEntity.class);
			if(hasRelation) {
				List<TableField> fields = this.fields(te.getTableName());
				te.setFields(fields);
			}
			result.add(te);
		}
		
		return result;
	}

	@Override
	public List<TableField> fields(String tableName) {
		List<TableField> result = new ArrayList<>();
		String sql = "select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_DEFAULT,t.NULLABLE,c.COMMENTS "
				+ "from USER_TAB_COLUMNS t,USER_COL_COMMENTS c "
				+ "where t.TABLE_NAME='"+tableName.toUpperCase()+"' "
				+ "and  c.TABLE_NAME=t.TABLE_NAME "
				+ "and c.COLUMN_NAME=t.COLUMN_NAME "
				+ "order by t.TABLE_NAME,t.COLUMN_ID";
		List<Row> list = Db.selectListBySql(sql);
		for (Row row : list) {
			Map<String,Object> data = row.toCamelKeysMap();
			String jsonStr = JSON.toJSONString(data);
			TableField item = JSON.parseObject(jsonStr, TableField.class);
			result.add(item);
		}		
		return result;
	}
	
	
	@Override
	public Set<String> fieldsKeys(String tableName,boolean isLowerCase) {
		Set<String> result = new HashSet<>();
		String sql = "select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_LENGTH,t.DATA_DEFAULT,t.NULLABLE,c.COMMENTS "
				+ "from USER_TAB_COLUMNS t,USER_COL_COMMENTS c "
				+ "where t.TABLE_NAME='DM_DOCUMENT' "
				+ "and  c.TABLE_NAME=t.TABLE_NAME "
				+ "and c.COLUMN_NAME=t.COLUMN_NAME "
				+ "order by t.TABLE_NAME,t.COLUMN_ID";
		List<Row> list = Db.selectListBySql(sql);
		list = RowConvertUtil.toRowListLowerKey(list);
		for (Row row : list) {
			String fieldName = row.getString("column_name");
			if(isLowerCase) {
				fieldName = fieldName.toLowerCase();
			}
			result.add(fieldName);
		}		
		return result;
	}

	@Override
	public void addField(String tableName, DmTypeAttr attr) {
		
		String updateSql = "alter table %tableName% add column %fieldname% %datatype%" ;
		updateSql = updateSql.replace("%tableName%", tableName);
		updateSql = updateSql.replace("%fieldname%", attr.getName());
		
		if(attr.getDataType().toLowerCase().startsWith("varchar")) {
			updateSql = updateSql.replace("%datatype%", attr.getDataType()+"("+attr.getDataLength()+")");
		}
		Db.updateBySql(updateSql);
		
		Db.updateBySql("comment on column "+tableName+"."+attr.getName()+" is '"+attr.getTitle()+"'");
	}
}
